Create database

dockerfile

FROM mysql:latest

ENV MYSQL_ROOT_PASSWORD=123456
ENV MYSQL_USER=user
ENV MYSQL_PASSWORD=123456

EXPOSE 3306

CMD ["mysqld"]

docker build -t ./dockerfile my_mysql
docker run -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 my_mysql mysqld
docker exec -it mysql bin/bash

mysql -u root -p

create dabasse test
use test

create table album (
  id int auto increment not null,
  title varchar(128) not null,
  artist varchar(255) not null,
  price decimal(5,2) not null
);

inser into album
  (title, artist, price)
VALUES
  ('Blue Train', 'John Coltrane', 56.99),
  ('Giant Steps', 'John Coltrane', 63.99),
  ('Jeru', 'Gerry Mulligan', 17.99),
  ('Sarah Vaughan', 'Sarah Vaughan', 34.98);

Connect Database

// main.go
package main

import (
  "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func main() {
  cfg := mysql.NewConfig()
	cfg.User = "root"
	cfg.Passwd = "123456"
	cfg.DBName = "test"
	cfg.Addr = "127.0.0.1:3306"
	cfg.Net = "tcp"

	var err error
	db, err = sql.Open("mysql", cfg.FormatDSN())
	if err != nil {
		log.Fatal(err)
	}

	pingErr := db.Ping()
	if pingErr != nil {
		log.Fatal(pingErr)
	}

	fmt.Println("mysql connected!")

	albums, err := queryAlbum(db, "John Coltrane")
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Albums found: %v\n", albums)
}

func queryAlbum(db *sql.DB, artist string) ([]Album, error) {
	var album []Album

	rows, err := db.Query("select * from album where artist = ?", artist)

	if err != nil {
		return nil, fmt.Errorf("AlbumByArtist %q %v", artist, err)
	}

	defer rows.Close()

	for rows.Next() {
		var alb Album
		if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
			return nil, fmt.Errorf("albumsByArtist %q: %v", artist, err)
		}
		album = append(album, alb)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("albumsByArtist %q: %v", artist, err)
	}

	return album, nil
}

• Use the MySQL driver’s Config – and the type’s FormatDSN -– to collect connection properties and format them into a DSN for a connection string.

• Call DB.Ping to confirm that connecting to the database works. At run time, sql.Open might not immediately connect, depending on the driver. You’re using Ping here to confirm that the database/sql package can connect when it needs to.

• Defer closing rows so that any resources it holds will be released when the function exits.

Query for a single row

func ablumByID(id int) (Album, error) {
	var alb Album
	row := db.QueryRow("select * from album where id = ?", id)

	if err := row.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
		if err == sql.ErrNoRows {
			return alb, fmt.Errorf("albumById %d not such album", id)
		}
		return alb, fmt.Errorf("albumBtId %d %v", id, err)
	}

	return alb, nil
}

• Use DB.QueryRow to execute a SELECT statement to query for an album with the specified ID.

Add data

func addAlbum(title string, artist string, price float32) (int64, error) {
	res, err := db.Exec("insert into album (title, artist, price) values (?, ? ,?)", title, artist, price)

	if err != nil {
		return -1, fmt.Errorf("add ablum error: %v", err)
	}

	id, err := res.LastInsertId()

	if err != nil {
		return -1, fmt.Errorf("add album error: %v", err)
	}

	return id, nil
}